﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Xml;
using System.Xml.XPath;

namespace Common.OpenXml
{
	/// <summary>
	/// Encapsulates the functionality of a chart and it's association drawing.
	/// </summary>
	public class Chart : PackagePartBase
	{
		private Chart(PackageHelper package, Uri uri)
			: base(package, uri)
		{ }

		/// <summary>
		/// Creates a new chart and it's associatied drawing.
		/// </summary>
		/// <param name="package">The package to creat the part in.</param>
		/// <param name="drawingID"></param>
		/// <param name="chartDef"></param>
		/// <param name="chartData"></param>
		public static Chart Create(PackageHelper package, int drawingID, ChartDefinition chartDef, DataTable chartData)
		{
			byte[] chartTemplate = WriteChartPlot(chartDef, chartData);

			// create the drawing package part
			Uri drawingUri =
				new Uri(string.Format("/xl/drawings/drawing{0}.xml", drawingID),
						UriKind.Relative);
			package.CreateNewPart(drawingUri,
				"application/vnd.openxmlformats-" +
				"officedocument.drawing+xml",
				Properties.Resources.ChartDrawingTemplate);

			// create chart package part
			Uri chartUri =
				new Uri(string.Format("/xl/charts/{0}.xml", chartDef.ChartName),
						UriKind.Relative);
			package.CreateNewPart(chartUri,
				"application/vnd.openxmlformats-" +
				"officedocument.drawingml.chart+xml",
				chartTemplate);

			// create the relationship to the data
			string relId =
				package.CreateInternalRelationship(drawingUri,
					chartUri,
					"http://schemas.openxmlformats.org/officeDocument/" +
					"2006/relationships/chart");

			// set the id of the contained chart
			XmlDocument drawingXml = package.GetWritablePart(drawingUri);
			drawingXml.CreateNavigator().SelectSingleNode(
				"xdr:wsDr/xdr:twoCellAnchor/xdr:graphicFrame" +
				"/a:graphic/a:graphicData/c:chart/@r:id",
				Namespaces.NamespaceManager)
					.SetValue(relId);
			package.SavePart(drawingUri, drawingXml);

			// return the new object
			return new Chart(package, drawingUri);
		}

		/// <summary>
		/// column name(s) used in categories, series are changed to formular
		/// </summary>
		/// <param name="chartDef"></param>
		/// <param name="chartData"></param>
		/// <returns></returns>
		private static byte[] WriteChartPlot(ChartDefinition chartDef, DataTable chartData)
		{
			XmlDocument chartXml =new XmlDocument();
			chartXml.LoadXml(Properties.Resources.ChartSpace);
			XPathNavigator chartSpace = chartXml.CreateNavigator()
				.SelectSingleNode("c:chartSpace",
					Namespaces.NamespaceManager);
			using (XmlWriter writer = chartSpace.AppendChild())
			{
				writer.WriteStartElement(Prefixes.ChartML,"chart",Namespaces.ChartML);
				// title 
				writer.WriteStartElement(Prefixes.ChartML, "title", Namespaces.ChartML);
                if(!string.IsNullOrEmpty(chartDef.Title))
                {
                    writer.WriteStartElement(Prefixes.ChartML, "tx", Namespaces.DrawingML);
                    writer.WriteStartElement(Prefixes.ChartML, "rich", Namespaces.DrawingML);

                    writer.WriteStartElement(Prefixes.DrawingML, "bodyPr", Namespaces.DrawingML);
                    writer.WriteEndElement();//bodyPr
                    writer.WriteStartElement(Prefixes.DrawingML, "lstStyle", Namespaces.DrawingML);
                    writer.WriteEndElement();//lstStyle
                    writer.WriteStartElement(Prefixes.DrawingML, "p", Namespaces.DrawingML);
                    writer.WriteStartElement(Prefixes.DrawingML, "pPr", Namespaces.DrawingML);
                    writer.WriteStartElement(Prefixes.DrawingML, "defRPr", Namespaces.DrawingML);
                    writer.WriteEndElement();//defRPr
                    writer.WriteEndElement();//pPr
                    writer.WriteStartElement(Prefixes.DrawingML, "r", Namespaces.DrawingML);
                    writer.WriteStartElement(Prefixes.DrawingML, "rPr", Namespaces.DrawingML);
                    writer.WriteAttributeString("lang", "en-US");
                    writer.WriteEndElement();//rPr
                    writer.WriteStartElement(Prefixes.DrawingML, "t", Namespaces.DrawingML);
                    writer.WriteString(chartDef.Title);
                    writer.WriteEndElement();//t
                    writer.WriteEndElement();//r
                    writer.WriteEndElement();//p

                    writer.WriteEndElement();//rich
                    writer.WriteEndElement();//tx
                }
                writer.WriteStartElement(Prefixes.ChartML, "layout", Namespaces.ChartML);
                writer.WriteEndElement(); //layout
				writer.WriteEndElement();// title

				// plotArea
				writer.WriteStartElement(Prefixes.ChartML, "plotArea", Namespaces.ChartML);
				writer.WriteStartElement(Prefixes.ChartML, "layout", Namespaces.ChartML);
				writer.WriteEndElement();//layout 

				if (chartDef.ChartType == ExcelChartType.PieChart)
				{
					WritePieChart(writer, chartDef, chartData);
				}
				else if(chartDef.ChartType==ExcelChartType.LineChart)
				{
					WriteLineChart(writer, chartDef, chartData);
				}
                else if(chartDef.ChartType==ExcelChartType.BarChart)
                {
                    WriteBarChart(writer, chartDef, chartData);
                }

                // plot area style 
                if(chartDef.ApplyStyle)
                {
                    ChartStyleHelper.AddPlotAreaStyle(writer);
                }
				
				writer.WriteEndElement();//plotArea

				// legend
				writer.WriteStartElement(Prefixes.ChartML, "legend", Namespaces.ChartML);
				writer.WriteStartElement(Prefixes.ChartML, "legendPos", Namespaces.ChartML);
				writer.WriteAttributeString("val","r");
				writer.WriteEndElement();//legendPos
				writer.WriteStartElement(Prefixes.ChartML, "layout", Namespaces.ChartML);
				writer.WriteEndElement();//layout
				writer.WriteEndElement();//legend

				writer.WriteEndElement(); // chart 

                // chart space style 
                if(chartDef.ApplyStyle)
                {
                    ChartStyleHelper.AddChartspaceStyle(writer);
                }
			}
			byte[] contents = Encoding.Default.GetBytes(chartXml.OuterXml);
			return contents;
		}

		private static void WriteLineChart(XmlWriter writer, ChartDefinition chartDef, DataTable chartData)
		{
			writer.WriteStartElement(Prefixes.ChartML, "lineChart", Namespaces.ChartML);
			writer.WriteStartElement(Prefixes.ChartML, "grouping", Namespaces.ChartML);
			writer.WriteAttributeString("val","standard");
			writer.WriteEndElement();//grouping
			int seriesIdx = 0;
			foreach (string seriesLabel in chartDef.SeriesColumns)
			{
				writer.WriteStartElement(Prefixes.ChartML, "ser", Namespaces.ChartML);
				writer.WriteStartElement(Prefixes.ChartML, "idx", Namespaces.ChartML);
				writer.WriteAttributeString("val", seriesIdx.ToString());
				writer.WriteEndElement(); //idx
				writer.WriteStartElement(Prefixes.ChartML, "order", Namespaces.ChartML);
				writer.WriteAttributeString("val", seriesIdx.ToString());
				writer.WriteEndElement(); //order
                writer.WriteStartElement(Prefixes.ChartML, "marker", Namespaces.ChartML);
                writer.WriteStartElement(Prefixes.ChartML, "symbol", Namespaces.ChartML);
                writer.WriteAttributeString("val", "none");
                writer.WriteEndElement();//symbol
                writer.WriteEndElement();//marker

				Regex formularRegex = new Regex(@"([^!]+)!\$(\S+)\$(\d+):\$(\S+)\$(\d+)");
				// cat 
				if (seriesIdx == 0)
				{
					writer.WriteStartElement(Prefixes.ChartML, "cat", Namespaces.ChartML);
					writer.WriteStartElement(Prefixes.ChartML, "strRef", Namespaces.ChartML);
					writer.WriteStartElement(Prefixes.ChartML, "f", Namespaces.ChartML);
					Match categoryFormularMatch = formularRegex.Match(chartDef.CategoryFormular);
					if (categoryFormularMatch.Success)
					{
						string worksheetName = categoryFormularMatch.Groups[1].Value;
						string colPos = categoryFormularMatch.Groups[2].Value;
						int fromRowNum = int.Parse(categoryFormularMatch.Groups[3].Value);
						string colPos2 = categoryFormularMatch.Groups[4].Value;
						//int toRowNum = int.Parse(categoryFormularMatch.Groups[5].Value);
						chartDef.CategoryFormular =
							string.Format("{0}!${1}${2}:${3}${4}",
							              worksheetName, colPos, fromRowNum, colPos2,
							              chartData.Rows.Count + fromRowNum - 1);
					}
					writer.WriteString(chartDef.CategoryFormular);
					writer.WriteEndElement(); //f
					writer.WriteStartElement(Prefixes.ChartML, "strCache", Namespaces.ChartML);
					writer.WriteStartElement(Prefixes.ChartML, "ptCount", Namespaces.ChartML);
					writer.WriteAttributeString("val", chartData.Rows.Count.ToString());
					writer.WriteEndElement(); //ptCount
					for (int i = 0; i < chartData.Rows.Count; i++)
					{
						writer.WriteStartElement(Prefixes.ChartML, "pt", Namespaces.ChartML);
						writer.WriteAttributeString("idx", i.ToString());
						writer.WriteStartElement(Prefixes.ChartML, "v", Namespaces.ChartML);
						if (chartData.Rows[i][seriesLabel] != null)
							writer.WriteString(chartData.Rows[i][chartDef.CategoryColumnName].ToString());
						else
							writer.WriteString("");
						writer.WriteEndElement(); //v
						writer.WriteEndElement(); //pt
					}
					writer.WriteEndElement(); //strCache
					writer.WriteEndElement(); //strRef
					writer.WriteEndElement(); //cat
				}

				// val 
				writer.WriteStartElement(Prefixes.ChartML, "val", Namespaces.ChartML);
				writer.WriteStartElement(Prefixes.ChartML, "numRef", Namespaces.ChartML);
				writer.WriteStartElement(Prefixes.ChartML, "f", Namespaces.ChartML);
                Match seriesFormularMatch = formularRegex.Match(chartDef.SeriesDataFormulars[seriesLabel]);
                if (seriesFormularMatch.Success)
                {
                    string worksheetName = seriesFormularMatch.Groups[1].Value;
                    string colPos = seriesFormularMatch.Groups[2].Value;
                    int fromRowNum = int.Parse(seriesFormularMatch.Groups[3].Value);
                    string colPos2 = seriesFormularMatch.Groups[4].Value;
                    //int toRowNum = int.Parse(seriesFormularMatch.Groups[5].Value);
                    string seriesFormular =
                        string.Format("{0}!${1}${2}:${3}${4}",
                                      worksheetName, colPos, fromRowNum, colPos2,
                                      chartData.Rows.Count + fromRowNum - 1);
                    chartDef.SeriesDataFormulars[seriesLabel] = seriesFormular;
                }
				writer.WriteString(chartDef.SeriesDataFormulars[seriesLabel]);
				writer.WriteEndElement();//f
				writer.WriteStartElement(Prefixes.ChartML, "numCache", Namespaces.ChartML);
				writer.WriteStartElement(Prefixes.ChartML, "formatCode", Namespaces.ChartML);
				writer.WriteString("General");
				writer.WriteEndElement();//formatCode
				writer.WriteStartElement(Prefixes.ChartML, "ptCount", Namespaces.ChartML);
				writer.WriteAttributeString("val",chartData.Rows.Count.ToString());
				writer.WriteEndElement();//ptCount
				for (int i = 0; i < chartData.Rows.Count; i++)
				{
					writer.WriteStartElement(Prefixes.ChartML, "pt", Namespaces.ChartML);
					writer.WriteAttributeString("idx",i.ToString());
					writer.WriteStartElement(Prefixes.ChartML, "v", Namespaces.ChartML);
					if (chartData.Rows[i][seriesLabel] != null)
						writer.WriteString(chartData.Rows[i][seriesLabel].ToString());
					else 
						writer.WriteString("");
					writer.WriteEndElement();//v
					writer.WriteEndElement(); //pt
				}
				writer.WriteEndElement();//numCache
				writer.WriteEndElement();//numRef
				writer.WriteEndElement();//val

				writer.WriteEndElement(); //ser

				seriesIdx++;
			}

            writer.WriteStartElement(Prefixes.ChartML, "dLbls", Namespaces.ChartML);
            writer.WriteEndElement();//dLbls
			writer.WriteStartElement(Prefixes.ChartML, "marker", Namespaces.ChartML);
			writer.WriteAttributeString("val","1");
			writer.WriteEndElement();//marker
			writer.WriteStartElement(Prefixes.ChartML, "axId", Namespaces.ChartML);
			writer.WriteAttributeString("val", "74099712");
			writer.WriteEndElement();//axId
			writer.WriteStartElement(Prefixes.ChartML, "axId", Namespaces.ChartML);
			writer.WriteAttributeString("val", "74113792");
			writer.WriteEndElement();//axId
			writer.WriteEndElement();//lineChart

			// catAx 
			writer.WriteStartElement(Prefixes.ChartML, "catAx", Namespaces.ChartML);
			writer.WriteStartElement(Prefixes.ChartML, "axId", Namespaces.ChartML);
			writer.WriteAttributeString("val", "74099712");
			writer.WriteEndElement();//axId
			writer.WriteStartElement(Prefixes.ChartML, "scaling", Namespaces.ChartML);
			writer.WriteStartElement(Prefixes.ChartML, "orientation", Namespaces.ChartML);
			writer.WriteAttributeString("val", "minMax");
			writer.WriteEndElement();//orientation
			writer.WriteEndElement();//scaling
			writer.WriteStartElement(Prefixes.ChartML, "axPos", Namespaces.ChartML);
			writer.WriteAttributeString("val","b");
			writer.WriteEndElement();//axPos
			writer.WriteStartElement(Prefixes.ChartML, "numFmt", Namespaces.ChartML);
			writer.WriteAttributeString("formatCode", "General"); // TODO: category label format 
			writer.WriteAttributeString("sourceLinked", "1");
			writer.WriteEndElement();//numFmt
			writer.WriteStartElement(Prefixes.ChartML, "tickLblPos", Namespaces.ChartML);
			writer.WriteAttributeString("val","nextTo");
			writer.WriteEndElement();//tickLblPos
			writer.WriteStartElement(Prefixes.ChartML, "crossAx", Namespaces.ChartML);
			writer.WriteAttributeString("val", "74113792");
			writer.WriteEndElement();//crossAx
			writer.WriteStartElement(Prefixes.ChartML, "crosses", Namespaces.ChartML);
			writer.WriteAttributeString("val", "autoZero");
			writer.WriteEndElement();//crosses
			writer.WriteStartElement(Prefixes.ChartML, "auto", Namespaces.ChartML);
			writer.WriteAttributeString("val","1");
			writer.WriteEndElement();//auto
			writer.WriteStartElement(Prefixes.ChartML, "lblAlgn", Namespaces.ChartML);
			writer.WriteAttributeString("val", "ctr");
			writer.WriteEndElement();//lblAlgn
			writer.WriteStartElement(Prefixes.ChartML, "lblOffset", Namespaces.ChartML);
			writer.WriteAttributeString("val","100");
			writer.WriteEndElement();
			writer.WriteEndElement();//catAx

			//valAx
			writer.WriteStartElement(Prefixes.ChartML, "valAx", Namespaces.ChartML);
			writer.WriteStartElement(Prefixes.ChartML, "axId", Namespaces.ChartML);
			writer.WriteAttributeString("val", "74113792");
			writer.WriteEndElement();//axId
			writer.WriteStartElement(Prefixes.ChartML, "scaling", Namespaces.ChartML);
			writer.WriteStartElement(Prefixes.ChartML, "orientation", Namespaces.ChartML);
			writer.WriteAttributeString("val", "minMax");
			writer.WriteEndElement();//orientation
			writer.WriteEndElement();//scaling
			writer.WriteStartElement(Prefixes.ChartML, "axPos", Namespaces.ChartML);
			writer.WriteAttributeString("val", "l");		// line 
			writer.WriteEndElement();//axPos
			writer.WriteStartElement(Prefixes.ChartML, "majorGridlines", Namespaces.ChartML);
			writer.WriteEndElement();//majorGridlines
			writer.WriteStartElement(Prefixes.ChartML, "numFmt", Namespaces.ChartML);
			writer.WriteAttributeString("formatCode", "General");
			writer.WriteAttributeString("sourceLinked", "1");
			writer.WriteEndElement();//numFmt
			writer.WriteStartElement(Prefixes.ChartML, "tickLblPos", Namespaces.ChartML);
			writer.WriteAttributeString("val", "nextTo");
			writer.WriteEndElement();//tickLblPos
			writer.WriteStartElement(Prefixes.ChartML, "crossAx", Namespaces.ChartML);
			writer.WriteAttributeString("val", "74099712");
			writer.WriteEndElement();//crossAx
			writer.WriteStartElement(Prefixes.ChartML, "crosses", Namespaces.ChartML);
			writer.WriteAttributeString("val", "autoZero");
			writer.WriteEndElement();
			writer.WriteStartElement(Prefixes.ChartML, "crossBetween", Namespaces.ChartML);
			writer.WriteAttributeString("val", "between");
			writer.WriteEndElement();//crossBetween
            writer.WriteEndElement();//valAx
		}

		private static void WritePieChart(XmlWriter writer, ChartDefinition chartDef, DataTable chartData)
		{
			writer.WriteStartElement(Prefixes.ChartML, "pieChart", Namespaces.ChartML);
			writer.WriteStartElement(Prefixes.ChartML, "varyColors", Namespaces.ChartML);
			writer.WriteAttributeString("val", "1");
			writer.WriteEndElement(); //varyColors

			// plotArea/pieChart/ser
			writer.WriteStartElement(Prefixes.ChartML, "ser", Namespaces.ChartML);
			writer.WriteStartElement(Prefixes.ChartML, "idx", Namespaces.ChartML);
			writer.WriteAttributeString("val", "0");
			writer.WriteEndElement(); //idx
			writer.WriteStartElement(Prefixes.ChartML, "order", Namespaces.ChartML);
			writer.WriteAttributeString("val", "0");
			writer.WriteEndElement(); // order
			writer.WriteStartElement(Prefixes.ChartML, "tx", Namespaces.ChartML);
			writer.WriteStartElement(Prefixes.ChartML, "strRef", Namespaces.ChartML);
			writer.WriteStartElement(Prefixes.ChartML, "strCache", Namespaces.ChartML);
			writer.WriteStartElement(Prefixes.ChartML, "ptCount", Namespaces.ChartML);
			writer.WriteAttributeString("val", "1");
			writer.WriteEndElement(); //ptCount
			writer.WriteStartElement(Prefixes.ChartML, "pt", Namespaces.ChartML);
			writer.WriteAttributeString("idx", "0");
			writer.WriteStartElement(Prefixes.ChartML, "v", Namespaces.ChartML);
			writer.WriteString(chartDef.Title);
			writer.WriteEndElement(); //v
			writer.WriteEndElement(); //pt
			writer.WriteEndElement(); //strCache
			writer.WriteEndElement(); //strRef
			writer.WriteEndElement(); //tx

			// plotArea/pie3DChart/ser/cat 
			writer.WriteStartElement(Prefixes.ChartML, "cat", Namespaces.ChartML);
			writer.WriteStartElement(Prefixes.ChartML, "strRef", Namespaces.ChartML);
			writer.WriteStartElement(Prefixes.ChartML, "f", Namespaces.ChartML);
			Regex formularRegex = new Regex(@"([^!]+)!\$(\S+)\$(\d+):\$(\S+)\$(\d+)");
			Match categoryFormularMatch = formularRegex.Match(chartDef.CategoryFormular);
			if (categoryFormularMatch.Success)
			{
				string worksheetName = categoryFormularMatch.Groups[1].Value;
				string colPos = categoryFormularMatch.Groups[2].Value;
				int fromRowNum = int.Parse(categoryFormularMatch.Groups[3].Value);
				string colPos2 = categoryFormularMatch.Groups[4].Value;
				//int toRowNum = int.Parse(categoryFormularMatch.Groups[5].Value);
				chartDef.CategoryFormular =
					string.Format("{0}!${1}${2}:${3}${4}",
								  worksheetName, colPos, fromRowNum, colPos2,
								  chartData.Rows.Count + fromRowNum - 1);
			}
			writer.WriteString(chartDef.CategoryFormular);
			writer.WriteEndElement(); //f
			writer.WriteStartElement(Prefixes.ChartML, "strCache", Namespaces.ChartML);
			writer.WriteStartElement(Prefixes.ChartML, "ptCount", Namespaces.ChartML);
			writer.WriteAttributeString("val", chartData.Rows.Count.ToString());
			writer.WriteEndElement(); //ptCount
			for (int i = 0; i < chartData.Rows.Count; i++)
			{
				writer.WriteStartElement(Prefixes.ChartML, "pt", Namespaces.ChartML);
				writer.WriteAttributeString("idx", i.ToString());
				writer.WriteStartElement(Prefixes.ChartML, "v", Namespaces.ChartML);
				string lbl = chartData.Rows[i][chartDef.CategoryColumnName] == null
								? ""
								: chartData.Rows[i][chartDef.CategoryColumnName].ToString();
				writer.WriteString(lbl);
				writer.WriteEndElement(); //v
				writer.WriteEndElement(); //pt
			}
			writer.WriteEndElement(); //strCache
			writer.WriteEndElement(); //strRef
			writer.WriteEndElement(); //cat

			// plotArea/pieChart/ser/val 
			writer.WriteStartElement(Prefixes.ChartML, "val", Namespaces.ChartML);
			writer.WriteStartElement(Prefixes.ChartML, "numRef", Namespaces.ChartML);
			writer.WriteStartElement(Prefixes.ChartML, "f", Namespaces.ChartML);
			Match seriesFormularMatch = formularRegex.Match(chartDef.SeriesDataFormulars[chartDef.SeriesColumns[0]]);
			if (seriesFormularMatch.Success)
			{
				string worksheetName = seriesFormularMatch.Groups[1].Value;
				string colPos = seriesFormularMatch.Groups[2].Value;
				int fromRowNum = int.Parse(seriesFormularMatch.Groups[3].Value);
				string colPos2 = seriesFormularMatch.Groups[4].Value;
				//int toRowNum = int.Parse(seriesFormularMatch.Groups[5].Value);
				string seriesFormular =
					string.Format("{0}!${1}${2}:${3}${4}",
								  worksheetName, colPos, fromRowNum, colPos2,
								  chartData.Rows.Count + fromRowNum - 1);
				chartDef.SeriesDataFormulars[chartDef.SeriesColumns[0]] = seriesFormular;
			}
			writer.WriteString(chartDef.SeriesDataFormulars[chartDef.SeriesColumns[0]]);
			writer.WriteEndElement(); //f
			writer.WriteStartElement(Prefixes.ChartML, "numCache", Namespaces.ChartML);
			writer.WriteStartElement(Prefixes.ChartML, "formatCode", Namespaces.ChartML);
			writer.WriteString("General");
			writer.WriteEndElement(); //formatCode
			writer.WriteStartElement(Prefixes.ChartML, "ptCount", Namespaces.ChartML);
			writer.WriteAttributeString("val", chartData.Rows.Count.ToString());
			writer.WriteEndElement(); //ptCount
			string seriesColName = chartDef.SeriesColumns[0];
			for (int i = 0; i < chartData.Rows.Count; i++)
			{
				writer.WriteStartElement(Prefixes.ChartML, "pt", Namespaces.ChartML);
				writer.WriteAttributeString("idx", i.ToString());
				writer.WriteStartElement(Prefixes.ChartML, "v", Namespaces.ChartML);
				writer.WriteString(chartData.Rows[i][seriesColName] == null ? "" : chartData.Rows[i][seriesColName].ToString());
				writer.WriteEndElement(); //v
				writer.WriteEndElement(); //pt
			}
			writer.WriteEndElement(); //numCache
			writer.WriteEndElement(); //numRef
			writer.WriteEndElement(); //val

			writer.WriteEndElement(); //ser

			writer.WriteStartElement(Prefixes.ChartML, "firstSliceAng", Namespaces.ChartML);
			writer.WriteAttributeString("val", "0");
			writer.WriteEndElement(); //firstSliceAng
			writer.WriteEndElement();//pieChart
		}

        private static void WriteBarChart(XmlWriter writer, ChartDefinition chartDef, DataTable chartData)
        {
            writer.WriteStartElement(Prefixes.ChartML, "barChart", Namespaces.ChartML);
            writer.WriteStartElement(Prefixes.ChartML, "barDir", Namespaces.ChartML);
            writer.WriteAttributeString("val", "col");
            writer.WriteEndElement();//barDir
            writer.WriteStartElement(Prefixes.ChartML, "grouping", Namespaces.ChartML);
            writer.WriteAttributeString("val", "clustered");
            writer.WriteEndElement();//grouping

            // series collection 
            int seriesIndex = 0;
			Regex formularRegex = new Regex(@"([^!]+)!\$(\S+)\$(\d+):\$(\S+)\$(\d+)");
            foreach (string seriesLabel in chartDef.SeriesColumns)
            {
                // ser
                writer.WriteStartElement(Prefixes.ChartML, "ser", Namespaces.ChartML);
                writer.WriteStartElement(Prefixes.ChartML, "idx", Namespaces.ChartML);
                writer.WriteAttributeString("val", seriesIndex.ToString());
                writer.WriteEndElement();
                writer.WriteStartElement(Prefixes.ChartML, "order", Namespaces.ChartML);
                writer.WriteAttributeString("val", seriesIndex.ToString());
                writer.WriteEndElement();//order

				if (seriesIndex == 0)
				{
					// cat
					writer.WriteStartElement(Prefixes.ChartML, "cat", Namespaces.ChartML);
					writer.WriteStartElement(Prefixes.ChartML, "strRef", Namespaces.ChartML);
					writer.WriteStartElement(Prefixes.ChartML, "f", Namespaces.ChartML);
					// update formular 
					Match categoryFormularMatch = formularRegex.Match(chartDef.CategoryFormular);
					if (categoryFormularMatch.Success)
					{
						string worksheetName = categoryFormularMatch.Groups[1].Value;
						string colPos = categoryFormularMatch.Groups[2].Value;
						int fromRowNum = int.Parse(categoryFormularMatch.Groups[3].Value);
						string colPos2 = categoryFormularMatch.Groups[4].Value;
						//int toRowNum = int.Parse(categoryFormularMatch.Groups[5].Value);
						chartDef.CategoryFormular =
							string.Format("{0}!${1}${2}:${3}${4}",
							              worksheetName, colPos, fromRowNum, colPos2,
							              chartData.Rows.Count + fromRowNum - 1);
					}
					writer.WriteString(chartDef.CategoryFormular);
					writer.WriteEndElement(); //f
					writer.WriteStartElement(Prefixes.ChartML, "strCache", Namespaces.ChartML);
					writer.WriteStartElement(Prefixes.ChartML, "ptCount", Namespaces.ChartML);
					writer.WriteAttributeString("val", chartData.Rows.Count.ToString());
					writer.WriteEndElement();
					for (int i = 0; i < chartData.Rows.Count; i++)
					{
						writer.WriteStartElement(Prefixes.ChartML, "pt", Namespaces.ChartML);
						writer.WriteAttributeString("idx", i.ToString());
						writer.WriteStartElement(Prefixes.ChartML, "v", Namespaces.ChartML);
						if (chartData.Rows[i][chartDef.CategoryColumnName] != null)
							writer.WriteString(chartData.Rows[i][chartDef.CategoryColumnName].ToString());
						else
							writer.WriteString("");
						writer.WriteEndElement(); //v
						writer.WriteEndElement(); //pt
					}
					writer.WriteEndElement(); //strCache
					writer.WriteEndElement(); //strRef
					writer.WriteEndElement(); //cat
				}

            	// val 
                writer.WriteStartElement(Prefixes.ChartML, "val", Namespaces.ChartML);
                writer.WriteStartElement(Prefixes.ChartML, "numRef", Namespaces.ChartML);
                writer.WriteStartElement(Prefixes.ChartML, "f", Namespaces.ChartML);
                Match seriesFormularMatch = formularRegex.Match(chartDef.SeriesDataFormulars[seriesLabel]);
                if (seriesFormularMatch.Success)
                {
                    string worksheetName = seriesFormularMatch.Groups[1].Value;
                    string colPos = seriesFormularMatch.Groups[2].Value;
                    int fromRowNum = int.Parse(seriesFormularMatch.Groups[3].Value);
                    string colPos2 = seriesFormularMatch.Groups[4].Value;
                    //int toRowNum = int.Parse(seriesFormularMatch.Groups[5].Value);
                    string seriesFormular =
                        string.Format("{0}!${1}${2}:${3}${4}",
                                      worksheetName, colPos, fromRowNum, colPos2,
                                      chartData.Rows.Count + fromRowNum - 1);
                    chartDef.SeriesDataFormulars[seriesLabel] = seriesFormular;
                }
                writer.WriteString(chartDef.SeriesDataFormulars[seriesLabel]);
                writer.WriteEndElement();//f
                writer.WriteStartElement(Prefixes.ChartML, "numCache", Namespaces.ChartML);
                writer.WriteStartElement(Prefixes.ChartML, "formatCode", Namespaces.ChartML);
                writer.WriteString("General");
                writer.WriteEndElement();//formatCode
                writer.WriteStartElement(Prefixes.ChartML, "ptCount", Namespaces.ChartML);
                writer.WriteAttributeString("val", chartData.Rows.Count.ToString());
                writer.WriteEndElement();//ptCount
                for(int i=0;i<chartData.Rows.Count;i++)
                {
                    writer.WriteStartElement(Prefixes.ChartML, "pt", Namespaces.ChartML);
                    writer.WriteAttributeString("idx", i.ToString());
                    writer.WriteStartElement(Prefixes.ChartML, "v", Namespaces.ChartML);
                    if(chartData.Rows[i][seriesLabel]!=null)
                        writer.WriteString(chartData.Rows[i][seriesLabel].ToString());
                    else 
                        writer.WriteString("");
                    writer.WriteEndElement();//v
                    writer.WriteEndElement();//pt
                }
                writer.WriteEndElement();//numCache
                writer.WriteEndElement();//numRef
                writer.WriteEndElement();//val

                writer.WriteEndElement(); //ser
                seriesIndex++;
            }

            writer.WriteStartElement(Prefixes.ChartML, "dLbls", Namespaces.ChartML);
            writer.WriteEndElement();//dLbls
            writer.WriteStartElement(Prefixes.ChartML, "axId", Namespaces.ChartML);
            writer.WriteAttributeString("val", "110798720");
            writer.WriteEndElement();//axId
            writer.WriteStartElement(Prefixes.ChartML, "axId", Namespaces.ChartML);
            writer.WriteAttributeString("val", "110800256");
            writer.WriteEndElement();//axId

            writer.WriteEndElement();//barChart

            // catAx
            writer.WriteStartElement(Prefixes.ChartML, "catAx", Namespaces.ChartML);
            writer.WriteStartElement(Prefixes.ChartML, "axId", Namespaces.ChartML);
            writer.WriteAttributeString("val", "110798720");
            writer.WriteEndElement();//axId
            writer.WriteStartElement(Prefixes.ChartML, "scaling", Namespaces.ChartML);
            writer.WriteStartElement(Prefixes.ChartML, "orientation", Namespaces.ChartML);
            writer.WriteAttributeString("val", "minMax");
            writer.WriteEndElement();//orientation
            writer.WriteEndElement();//scaling
            writer.WriteStartElement(Prefixes.ChartML, "axPos", Namespaces.ChartML);
            writer.WriteAttributeString("val", "b");
            writer.WriteEndElement();//axPos
            writer.WriteStartElement(Prefixes.ChartML, "numFmt", Namespaces.ChartML);
            writer.WriteAttributeString("formatCode", "General");
            writer.WriteAttributeString("sourceLinked", "1");
            writer.WriteEndElement();//numFmt
            writer.WriteStartElement(Prefixes.ChartML, "majorTickMark", Namespaces.ChartML);
            writer.WriteAttributeString("val", "none");
            writer.WriteEndElement();//majorTickMark
            writer.WriteStartElement(Prefixes.ChartML, "tickLblPos", Namespaces.ChartML);
            writer.WriteAttributeString("val", "nextTo");
            writer.WriteEndElement();//tickLblPos
            writer.WriteStartElement(Prefixes.ChartML, "crossAx", Namespaces.ChartML);
            writer.WriteAttributeString("val", "110800256");
            writer.WriteEndElement();//crossAx
            writer.WriteStartElement(Prefixes.ChartML, "crosses", Namespaces.ChartML);
            writer.WriteAttributeString("val", "autoZero");
            writer.WriteEndElement();//crosses
            writer.WriteStartElement(Prefixes.ChartML, "auto", Namespaces.ChartML);
            writer.WriteAttributeString("val", "1");
            writer.WriteEndElement();//auto
            writer.WriteStartElement(Prefixes.ChartML, "lblAlgn", Namespaces.ChartML);
            writer.WriteAttributeString("val", "ctr");
            writer.WriteEndElement();//lblAlgn
            writer.WriteStartElement(Prefixes.ChartML, "lblOffset", Namespaces.ChartML);
            writer.WriteAttributeString("val", "100");
            writer.WriteEndElement();//lblOffset
            writer.WriteEndElement();//catAx

            // valAx
            writer.WriteStartElement(Prefixes.ChartML, "valAx", Namespaces.ChartML);
            writer.WriteStartElement(Prefixes.ChartML, "axId", Namespaces.ChartML);
            writer.WriteAttributeString("val", "110800256");
            writer.WriteEndElement();//axId
            writer.WriteStartElement(Prefixes.ChartML, "scaling", Namespaces.ChartML);
            writer.WriteStartElement(Prefixes.ChartML, "orientation", Namespaces.ChartML);
            writer.WriteAttributeString("val", "minMax");
            writer.WriteEndElement();//orientation
            writer.WriteEndElement();//scaling
            writer.WriteStartElement(Prefixes.ChartML, "axPos", Namespaces.ChartML);
            writer.WriteAttributeString("val", "l");
            writer.WriteEndElement();//axPos
            writer.WriteStartElement(Prefixes.ChartML, "majorGridlines", Namespaces.ChartML);
            writer.WriteEndElement();//majorGridlines
            writer.WriteStartElement(Prefixes.ChartML, "numFmt", Namespaces.ChartML);
            writer.WriteAttributeString("formatCode", "General");
            writer.WriteAttributeString("sourceLinked", "1");
            writer.WriteEndElement();//numFmt
            writer.WriteStartElement(Prefixes.ChartML, "majorTickMark", Namespaces.ChartML);
            writer.WriteAttributeString("val", "none");
            writer.WriteEndElement();//majorTickMark
            writer.WriteStartElement(Prefixes.ChartML, "tickLblPos", Namespaces.ChartML);
            writer.WriteAttributeString("val", "nextTo");
            writer.WriteEndElement();//tickLblPos
            writer.WriteStartElement(Prefixes.ChartML, "crossAx", Namespaces.ChartML);
            writer.WriteAttributeString("val", "110798720");
            writer.WriteEndElement();//crossAx
            writer.WriteStartElement(Prefixes.ChartML, "crosses", Namespaces.ChartML);
            writer.WriteAttributeString("val", "autoZero");
            writer.WriteEndElement();//crosses
            writer.WriteStartElement(Prefixes.ChartML, "crossBetween", Namespaces.ChartML);
            writer.WriteAttributeString("val", "between");
            writer.WriteEndElement();//crossBetween
            writer.WriteEndElement();//valAx
        }
	}
}
